package com.ruoyi.bearing.service.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class ExcelProcessor {

    public static void main(String[] args) {
        // 原始 Excel 文件路径
        String dataFilePath = "C:\\upload\\TestQ_1.xlsx";
        // 新生成的 Excel 文件路径
        String pretreatmentFile = "C:\\upload\\Processed_TestQ_1.xlsx";

        try {
            // 读取原始 Excel 文件
            FileInputStream fileInputStream = new FileInputStream(dataFilePath);
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 获取倒数第 2 列和倒数第 3 列的索引
            int lastColumnIndex = sheet.getRow(0).getLastCellNum() - 1; // 倒数第 1 列的索引
            int columnGIndex = lastColumnIndex - 1; // 倒数第 2 列的索引
            int columnFIndex = lastColumnIndex - 2; // 倒数第 3 列的索引

            // 存储计算结果
            List<BigDecimal> results = new ArrayList<>();

            // 创建 DataFormatter 实例
            DataFormatter dataFormatter = new DataFormatter();

            // 遍历每一行，进行计算
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过标题行

                Cell cellF = row.getCell(columnFIndex);
                Cell cellG = row.getCell(columnGIndex);

                // 获取单元格值并转换为 BigDecimal
                BigDecimal valueF = new BigDecimal(dataFormatter.formatCellValue(cellF));
                BigDecimal valueG = new BigDecimal(dataFormatter.formatCellValue(cellG));

                // 计算倒数第 3 列除以倒数第 2 列
                BigDecimal result = valueF.divide(valueG, 10, BigDecimal.ROUND_HALF_UP); // 保留 10 位小数
                results.add(result);
            }

            // 创建新的 Excel 文件
            Workbook newWorkbook = new XSSFWorkbook();
            Sheet newSheet = newWorkbook.createSheet("Processed Data");

            // 复制原始数据到新文件
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row newRow = newSheet.createRow(rowIndex);

                Row oldRow = sheet.getRow(rowIndex);
                if (oldRow != null) {
                    for (int colIndex = 0; colIndex < oldRow.getLastCellNum(); colIndex++) {
                        Cell oldCell = oldRow.getCell(colIndex);
                        Cell newCell = newRow.createCell(colIndex);
                        newCell.setCellValue(dataFormatter.formatCellValue(oldCell));
                    }
                }
            }

            // 添加计算结果到新列
            Row headerRow = newSheet.getRow(0);
            headerRow.createCell(lastColumnIndex + 1).setCellValue("R_DS(on)");
            // 设置标题行的字体为加粗
            Font font = newWorkbook.createFont();
            font.setBold(true); // 设置加粗

            CellStyle style = newWorkbook.createCellStyle();
            style.setFont(font);

            // 应用加粗样式到标题行的所有单元格
            for (int i = 0; i <= lastColumnIndex + 1; i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(style);
                }
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row dataRow = newSheet.getRow(i);
                dataRow.createCell(lastColumnIndex + 1).setCellValue(results.get(i - 1).doubleValue());
            }

            // 写入新的 Excel 文件
            FileOutputStream fileOutputStream = new FileOutputStream(pretreatmentFile);
            newWorkbook.write(fileOutputStream);
            fileOutputStream.close();

            System.out.println("处理完成，新文件已保存到: " + pretreatmentFile);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
